#!/bin/bash
# File Name: analysis_binlog.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-11 18:34:42
# Function: Analysis binlog for MySQL
#########################################################################
binlog_dir="/data/mysql/binlog/3306"
file="mysql-bin.000798"
mysqlbinlog="mysqlbinlog"
show="${1}"
${mysqlbinlog}  --base64-output=decode-rows -vv ${binlog_dir}/${file}|awk -v show_opt="${show}" 'BEGIN {
    table=1;
    trs_count=0;
	trs_insert_count=0;
	trs_update_count=0;
	trs_delete_count=0;
	show_time="Last Time";
	show_table="Table";
	show_type="Type";
	show_count="affected(row)";
    db_name="";
	t_name="";
	show_total="Trans(total)";
	show_insert="Insert(s)";
	show_update="Update(s)";
	show_delete="Delete(s)";
	s_type=""; 
	s_count=0;
	count=0;
	insert_count=0;
	update_count=0;
	delete_count=0;
	flag=0;
}
{
	if(match($0, /^#.*server id.*Table_map:.*mapped to number/)) 
	{
		split($(NF-4),a,"`");
		db_name=a[2];
		t_name=a[4];
		t_name_tmp=(db_name"."t_name)
		flag=1
		t_time=(substr($1,2,6)" "$2)
	}
	else if (match($0, /(### INSERT INTO .*\..*)/)) 
	{
		count=count+1;
		insert_count=insert_count+1;
		s_type="INSERT"; 
		s_count=s_count+1;
	}
	else if (match($0, /(### UPDATE .*\..*)/)) 
	{
		count=count+1;
		update_count=update_count+1;
		s_type="UPDATE"; 
		s_count=s_count+1;
	}
	else if (match($0, /(### DELETE FROM .*\..*)/)) 
	{
		count=count+1;
		delete_count=delete_count+1;
		s_type="DELETE"; 
		s_count=s_count+1;
	}
	else if (match($0, /^(# at) /) && flag==1 && s_count>0) 
	{
		opt_insert=(t_name_tmp"_insert");
		opt_update=(t_name_tmp"_update");
		opt_delete=(t_name_tmp"_delete");
		opt_time=(t_name_tmp"_time");
		if (table>1)
		{
			mark=0
			for (table_tmp=1;table_tmp<=table;table_tmp++)
			{
				if (index(t_name_array[table_tmp],t_name_tmp))
				{
					mark=1
					break
				}
			}
			if(mark==0)
			{
				res[opt_insert]=0;
				res[opt_update]=0;
				res[opt_delete]=0;
				res[opt_time]="";
		        t_name_array[table]=t_name_tmp;
			    table++;
				mark=0;
			}
		}
	    else if(table==1)
		{
			res[opt_insert]=0;
			res[opt_update]=0;
			res[opt_delete]=0;
			res[opt_time]=""
			t_name_array[1]=t_name_tmp;
			table++;
		}
		if (s_type=="DELETE")
		{
			res[opt_delete]+=s_count;
		}
		else if (s_type=="UPDATE")
		{
			res[opt_update]+=s_count;
		}
		else if (s_type=="INSERT")
		{
			res[opt_insert]+=s_count;
		}
		res[opt_time]=t_time;
		if (show_opt=="all")
		{
			printf "\033[32m%-20s%-60s%-15s%-15s%-15s%-15s%-15s%-15s\n\033[0m",t_time,t_name_tmp,s_type,s_count,trs_count,res[opt_insert],res[opt_update],res[opt_delete];
		}
		s_type=""; 
		s_count=0; 
	}
	else if (match($0, /^(COMMIT)/)) 
	{
		trs_count+=1;
		count=0;
		trs_insert_count+=insert_count;
		trs_update_count+=update_count;
		trs_delete_count+=delete_count;
		insert_count=0;
		update_count=0; 
		delete_count=0;
		s_type=""; 
		s_count=0; 
		flag=0;
	}
} END{
    t_name_array_length=length(t_name_array)
	printf "\033[35m%-60s%-30s%-15s%-15s%-15s\033[0m\n",show_table,show_time,show_insert,show_update,show_delete;
	sum_insert=0;
	sum_delete=0;
	sum_update=0;
	for (i=1;i<=t_name_array_length;i++)
	{
		opt_insert=(t_name_array[i]"_insert");
		opt_update=(t_name_array[i]"_update");
		opt_delete=(t_name_array[i]"_delete");
		opt_time=(t_name_array[i]"_time");
	    sum_insert+=res[opt_insert];
		sum_delete+=res[opt_delete];
		sum_update+=res[opt_update];
		printf "\033[32m%-60s%-30s%-15s%-15s%-15s\n\033[0m",t_name_array[i],res[opt_time],res[opt_insert],res[opt_update],res[opt_delete];
	}
	print ""
	printf "\033[35m%-60s%-30s%-15s%-15s\033[0m\n",show_total,show_insert,show_update,show_delete;
	printf "\033[32m%-60s%-30s%-15s%-15s\n\033[0m",trs_count,sum_insert,sum_update,sum_delete;
}'

